from time import sleep

from django.db import transaction
from openpyxl import load_workbook, Workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font, Border, Alignment, PatternFill  # 改为了 PatternFill
import math
import os
import django

import numpy as np
import warnings

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'DifyDjango.settings')  # 替换为你的settings模块路径
django.setup()

from Dify.tools.tools import *
from Dify.models.BOM import *


# 合并两列数据
def merge_data():
    # 加载Excel文件
    wb = load_workbook(r'C:\Users\Administrator\Desktop\原材料库存.xlsx')
    ws = wb.worksheets[0]

    # 从第4行开始处理
    for row in range(4, ws.max_row + 1):
        # 获取两列数据
        data1 = ws.cell(row=row, column=5).value  # 假设第一列是A列
        data2 = ws.cell(row=row, column=6).value  # 假设第二列是B列

        # 合并数据并在中间添加*
        if data1 is None:
            merged_data = f"{data2}"
        elif data2 is None:
            merged_data = f"{data1}"
        else:
            merged_data = f"{data1}*{data2}"

        # 将合并后的数据写入新列（假设写入C列）
        ws.cell(row=row, column=10).value = merged_data

    # 保存为新文件
    wb.save('文件2.xlsx')


# 分割excel文件
def split_excel_with_format(input_file, output_folder, chunk_size=300, header_rows=2):
    """
    分割Excel文件并保留完整格式（包括表头格式）

    参数:
        input_file: 输入Excel文件路径
        output_folder: 输出文件夹路径
        chunk_size: 每个文件的数据行数(默认300)
        header_rows: 表头行数(默认2行)
    """
    # 创建输出文件夹
    os.makedirs(output_folder, exist_ok=True)

    # 加载源文件（保留格式）
    wb = load_workbook(input_file)
    ws = wb.active

    # 获取总行数
    total_rows = ws.max_row - header_rows
    num_files = math.ceil(total_rows / chunk_size)

    print(f"总数据行数: {total_rows} (不含表头)")
    print(f"将分割为 {num_files} 个文件，每个文件最多 {chunk_size} 行数据")

    # 保存表头样式（存储每列的样式）
    header_styles = {}
    for col in range(1, ws.max_column + 1):
        col_styles = []
        for row in range(1, header_rows + 1):
            cell = ws.cell(row=row, column=col)

            # 修改点：使用 PatternFill 而不是 Fill
            col_styles.append({
                'font': Font(
                    name=cell.font.name,
                    size=cell.font.size,
                    bold=cell.font.bold,
                    italic=cell.font.italic,
                    color=cell.font.color),
                'border': Border(
                    left=cell.border.left,
                    right=cell.border.right,
                    top=cell.border.top,
                    bottom=cell.border.bottom),
                # 使用 PatternFill 而不是 Fill
                'fill': PatternFill(
                    patternType=cell.fill.patternType,
                    fgColor=cell.fill.fgColor),
                'number_format': cell.number_format,
                'alignment': Alignment(
                    horizontal=cell.alignment.horizontal,
                    vertical=cell.alignment.vertical,
                    wrap_text=cell.alignment.wrap_text),
                'value': cell.value
            })
        header_styles[col] = col_styles

    # 保存列宽
    col_widths = {}
    for col in range(1, ws.max_column + 1):
        col_letter = get_column_letter(col)
        col_dimension = ws.column_dimensions[col_letter]
        if col_dimension.width is not None:
            col_widths[col] = col_dimension.width

    # 分割文件
    for file_num in range(num_files):
        # 创建新工作簿
        new_wb = Workbook()
        new_ws = new_wb.active
        new_ws.title = "Data"

        # 设置列宽
        for col, width in col_widths.items():
            col_letter = get_column_letter(col)
            new_ws.column_dimensions[col_letter].width = width

        # 复制表头（带格式）
        for col, styles in header_styles.items():
            for row_idx, style in enumerate(styles, start=1):
                cell = new_ws.cell(row=row_idx, column=col, value=style['value'])
                cell.font = style['font']
                cell.border = style['border']
                cell.fill = style['fill']  # 这里使用 PatternFill
                cell.number_format = style['number_format']
                cell.alignment = style['alignment']

        # 计算数据行范围
        start_row = header_rows + 1 + file_num * chunk_size
        end_row = min(start_row + chunk_size, ws.max_row + 1)

        # 复制数据行（带格式）
        for row in range(start_row, end_row):
            for col in range(1, ws.max_column + 1):
                src_cell = ws.cell(row=row, column=col)
                dest_row = row - start_row + header_rows + 1
                dest_cell = new_ws.cell(row=dest_row, column=col, value=src_cell.value)

                # 复制样式（使用PatternFill）
                if src_cell.has_style:
                    # 字体
                    dest_cell.font = Font(
                        name=src_cell.font.name,
                        size=src_cell.font.size,
                        bold=src_cell.font.bold,
                        italic=src_cell.font.italic,
                        color=src_cell.font.color)

                    # 边框
                    dest_cell.border = Border(
                        left=src_cell.border.left,
                        right=src_cell.border.right,
                        top=src_cell.border.top,
                        bottom=src_cell.border.bottom)

                    # 填充 - 使用 PatternFill
                    dest_cell.fill = PatternFill(
                        patternType=src_cell.fill.patternType,
                        fgColor=src_cell.fill.fgColor)

                    # 数字格式和对齐
                    dest_cell.number_format = src_cell.number_format
                    dest_cell.alignment = Alignment(
                        horizontal=src_cell.alignment.horizontal,
                        vertical=src_cell.alignment.vertical,
                        wrap_text=src_cell.alignment.wrap_text)

        # 保存文件
        output_path = os.path.join(output_folder, f"part_{file_num + 1}.xlsx")
        new_wb.save(output_path)
        print(f"已创建: {output_path} (包含 {end_row - start_row} 行数据)")


# 修改期初数量
def replace_zeros_with_openpyxl(input_file, column_name, output_file=None, sheet_name=None):
    """
    使用 openpyxl 将 Excel 文件中指定列的所有 0 值替换为 11119

    参数:
        input_file: 输入 Excel 文件路径
        column_name: 要修改的列名称（表头在第二行）
        output_file: 输出文件路径（可选），默认为修改原文件
        sheet_name: 要处理的工作表名称（可选）
    """
    # 备份检查：如果输出文件未指定，创建原文件的备份
    if output_file is None:
        base, ext = os.path.splitext(input_file)
        backup_file = f"{base}_备份{ext}"

        # 创建备份
        import shutil
        shutil.copy2(input_file, backup_file)
        print(f"已创建备份文件: {backup_file}")

        output_file = input_file

    print(f"开始处理文件: {input_file}")

    try:
        # 加载工作簿
        wb = load_workbook(filename=input_file, data_only=True)

        # 选择工作表
        if sheet_name:
            if sheet_name in wb.sheetnames:
                ws = wb[sheet_name]
            else:
                raise ValueError(f"工作表 '{sheet_name}' 不存在。可用工作表: {wb.sheetnames}")
        else:
            ws = wb.active
            sheet_name = ws.title

        print(f"正在处理工作表: '{sheet_name}'")

        # 在第二行查找列名（表头行）
        header_row = 2
        col_index = None

        # 查找列名的位置
        for cell in ws[header_row]:
            if cell.value == column_name:
                col_index = cell.column
                break

        if col_index is None:
            # 获取所有列名
            available_columns = []
            for cell in ws[header_row]:
                if cell.value:
                    available_columns.append(cell.value)

            raise ValueError(
                f"列名 '{column_name}' 未在工作表 '{sheet_name}' 的第二行找到。"
                f"可用列名: {', '.join(available_columns)}"
            )

        # 获取列字母（如'A', 'B'）
        col_letter = cell.column_letter
        print(f"找到列 '{column_name}' 位于列 {col_letter}")

        # 统计和替换零值
        zero_count = 0

        # 从第三行开始（数据行）
        for row in range(header_row + 1, ws.max_row + 1):
            cell = ws.cell(row=row, column=col_index)

            # 检查数值类型（支持多种零值表示）
            if cell.data_type == 'n' and cell.value == 0:  # 整数或浮点数的0
                cell.value = 11119
                zero_count += 1
            elif cell.data_type == 's' and cell.value and cell.value.strip() == "0":  # 字符串"0"
                cell.value = 11119
                zero_count += 1
            elif cell.data_type == 'b' and cell.value is False:  # 布尔False
                cell.value = 11119
                zero_count += 1
            elif cell.value is None:  # 空值
                pass  # 不处理空值

        print(f"已替换 {zero_count} 个零值")

        # 保存修改
        wb.save(output_file)
        print(f"文件已保存: {output_file}")

        if output_file != input_file:
            print(f"原始文件未修改: {input_file}")

        return zero_count

    except Exception as e:
        print(f"处理过程中发生错误: {str(e)}")
        raise


# 修改后缀名称
def batch_rename_extensions(directory, old_ext, new_ext):
    """
    批量修改指定文件夹中文件的后缀名
    参数:
        directory: 目标文件夹路径
        old_ext: 原始后缀（不含点号）
        new_ext: 新后缀（不含点号）
    """
    # 2. 校验输入格式
    if not os.path.isdir(directory):
        print(f"错误：'{directory}' 不是有效文件夹路径")
        return

    # 3. 规范后缀格式（添加点号）
    old_ext = f".{old_ext}" if old_ext else ""
    new_ext = f".{new_ext}" if new_ext else ""

    # 4. 遍历文件并筛选匹配项
    matched_files = []
    for root, _, files in os.walk(directory):
        for filename in files:
            file_path = os.path.join(root, filename)

            # 使用系统方法正确处理文件后缀
            base_name, file_extension = os.path.splitext(filename)

            # 匹配条件：后缀完全一致
            if file_extension.lower() == old_ext.lower():
                matched_files.append({
                    "old_path": file_path,
                    "new_name": f"{base_name}{new_ext}"
                })

    # 5. 结果预览与确认
    print(f"\n找到 {len(matched_files)} 个匹配文件:")
    for i, file in enumerate(matched_files[:5], 1):
        print(f"{i}. {os.path.basename(file['old_path'])} → {file['new_name']}")

    if len(matched_files) > 5:
        print(f"...省略显示 {len(matched_files) - 5} 个文件")

    if not matched_files:
        print("没有找到匹配文件！")
        return

    # 7. 执行重命名操作
    success_count = 0
    for file in matched_files:
        try:
            new_path = os.path.join(os.path.dirname(file["old_path"]), file["new_name"])
            os.rename(file["old_path"], new_path)
            success_count += 1
        except Exception as e:
            print(f"重命名失败 {file['old_path']}: {str(e)}")

    # 8. 结果报告
    print(f"\n操作完成！成功修改: {success_count}/{len(matched_files)} 个文件")


# 获取指定列内容
def extract_unique_column_data(input_file, column_identifier, start_row, output_file):
    """
    提取Excel文件中指定列的数据，去除重复项并保存到新文件

    参数:
        input_file: 输入Excel文件路径
        column_identifier: 要提取的列（列名或列索引）
        start_row: 数据开始行号（Excel中的行号，从1开始）
        output_file: 输出文件路径
    """
    try:
        print(f"开始处理文件: {input_file}")

        # 读取Excel文件，跳过开始行之前的内容
        # 注意: read_excel的skiprows参数接受的是0-based索引
        skip_rows = start_row - 1  # 跳过前 start_row-1 行

        # 读取文件但不设表头
        df = pd.read_excel(input_file, header=None, skiprows=skip_rows)

        print(f"文件读取成功，数据从第{start_row}行开始")
        print(f"读取到 {len(df)} 行数据")

        # 确定列索引
        if isinstance(column_identifier, int):
            # 如果输入的是整数索引
            col_index = column_identifier
            if col_index >= len(df.columns) or col_index < 0:
                available_columns = list(range(len(df.columns)))
                raise ValueError(f"列索引 {col_index} 超出范围。可用列索引: {available_columns}")
        else:
            # 如果输入的是列名
            # 查找第一行(现在是header=None所以行0)是否有匹配的列名
            col_found = False
            for idx, value in enumerate(df.iloc[0]):
                if value == column_identifier:
                    col_index = idx
                    col_found = True
                    break

            if not col_found:
                available_columns = df.iloc[0].tolist()
                raise ValueError(f"列名 '{column_identifier}' 未找到。可用列名: {available_columns}")

        # 提取指定列数据
        column_data = df[col_index]
        print(f"提取列索引: {col_index} 数据")

        # 去除重复项并保留顺序
        unique_values = pd.Series(column_data.drop_duplicates())

        # 创建包含唯一值的新DataFrame
        result_df = pd.DataFrame(unique_values, columns=[column_identifier])

        # 显示处理结果
        print(f"原数据行数: {len(column_data)}")
        print(f"去重后行数: {len(result_df)}")

        # 保存结果
        result_df.to_excel(output_file, index=False)
        print(f"结果已保存到: {output_file}")

        return len(result_df)

    except Exception as e:
        print(f"处理过程中发生错误: {str(e)}")
        raise


# 合并长度至原材料
def process_excel(input_file, output_file):
    warnings.filterwarnings("ignore")

    # 1. 读取原材料长度数据
    try:
        # 跳过前2行，使用第二行作为列名
        raw_material_length = pd.read_excel(r"C:\Users\Administrator\Desktop\原材料长度.xlsx", sheet_name="原材料 旧版",
                                            header=5)

        # 清理数据：提取编码和卷长度列
        length_data = raw_material_length[["编码", "卷长度"]].copy()

        # 清理编码：转为字符串，去除空格
        length_data["编码"] = length_data["编码"].astype(str).str.strip()

        # 过滤无效行（卷长度有效且不为零）
        length_data = length_data[length_data["卷长度"].apply(lambda x: not pd.isna(x) and x > 0)]

        # 创建编码->卷长度的映射字典
        length_map = length_data.set_index("编码")["卷长度"].to_dict()

        print(f"成功加载 {len(length_map)} 条有效的卷长度映射关系")

    except Exception as e:
        print(f"加载原材料长度数据时出错: {e}")
        exit()

    # 2. 处理原材料库存数据
    try:
        # 加载库存数据
        inventory = pd.read_excel(r"C:\Users\Administrator\Desktop\信息\原材料.xlsx", header=1)

        print(f"成功加载 {len(inventory)} 条库存记录")

        # 检查必要列是否存在
        if "*商品编号" not in inventory.columns:
            print("错误：库存文件中缺少 '编码' 列")
            exit()

        # 统一更新计量单位
        inventory["计量单位"] = "卷/米"

        # 记录没有匹配的编码
        missing_codes = []

        # 更新换算率
        def update_conversion_rate(row):
            code = str(row["*商品编号"]).strip()

            if code in length_map:
                return "1/" + str(int(length_map[code]))
            else:
                missing_codes.append(code)
                # 如果没有匹配项，保留原始值（如果存在）
                return row["换算率"] if "换算率" in row else np.nan

        inventory["换算率"] = inventory.apply(update_conversion_rate, axis=1)

        # 保存处理后的库存数据
        output_file = r"C:\Users\Administrator\Desktop\信息\更新后_原材料.xlsx"
        inventory.to_excel(output_file, index=False)

        print(f"成功更新 {len(inventory)} 条库存记录")
        print(f"已保存到: {output_file}")

        # 打印没有匹配的编码（如果有）
        if missing_codes:
            unique_missing = set(missing_codes)
            print(f"\n警告: 以下编码未能匹配到卷长度 ({len(unique_missing)}个):")

    except Exception as e:
        print(f"处理库存数据时出错: {e}")


# 添加bom清单
@transaction.atomic
def addBom(input_file):
    print(f"开始读取Excel文件: {input_file}")
    with transaction.atomic():
        # 读取Excel文件
        df = pd.read_excel(input_file, header=1)
        arr_list = df.to_numpy()  # 转为NumPy数组
        now = int(time.time())
        for arr in arr_list:
            bom_code = getOrderCode(29)
            # 构建bom对象
            bom = {
                "purchase_code": bom_code,
                "name": "生产" + arr[2],
                "goods_name": arr[2],
                "goods_code": arr[1],
                "number": 0,
                "createtime": now,
                "updatetime": now
            }

            bom_ser = FaKuyouBomSerializer(data=bom)
            bom_ser.is_valid(raise_exception=True)
            bom_ser.save()

            # 查询所有车间
            workshop_list = FaKuyouWorkshop.objects.all()
            workshop = {}
            for i in workshop_list:
                workshop[i.name] = i.id
            # 五道工序
            process_list = []
            for i in ['印刷', '复合', '分切', '折边', '制袋']:
                process_code = getOrderCode(30)
                bom_process = {
                    "bom_id": bom_ser.data['id'],
                    "bom_code": process_code,
                    "name": i,
                    "wages": 0,
                    "hours": 0,
                    "createtime": now,
                    "updatetime": now
                }
                if i == '印刷':
                    if arr[7] == '大机':
                        bom_process['remark'] = '首工序：大机'
                    elif arr[7] == '小机':
                        bom_process['remark'] = '首工序：小机'
                    elif arr[7] == '宽机':
                        bom_process['remark'] = '首工序：宽机'
                elif i == arr[7]:
                    bom_process['remark'] = '首工序：'+arr[7]
                else:
                    if arr[7] == '包装':
                        bom_process['remark'] = '首工序：包装'
                    elif arr[7] == '仓库':
                        bom_process['remark'] = '首工序：仓库'
                    elif arr[7] == '外协':
                        bom_process['remark'] = '首工序：外协'
                process_ser = FaKuyouBomProcedureSerializer(data=bom_process)
                process_ser.is_valid(raise_exception=True)
                process_ser.save()
                process_list.append(process_ser.data)

            # 添加工序所需商品

            # 工序2：复合料1
            if arr[16] is not None:
                status, goods = getGoodsInfoByCodeOrName({"goods_code": arr[16]})
                if status:
                    bom_info = {
                        'bom_id': bom_ser.data['id'],
                        'procedure_id': process_list[1]['id'],
                        'goods_id': goods['id'],
                        'goods_code': goods['goods_code'],
                        'goods_name': goods['goods_name'],
                        'unit_id': goods['unit_id'],
                        'default_unit': goods['unit_id'],
                        'purchase': 0,
                        'number': 1,
                        'basic_nmber': 1,
                        'money': 0,
                        'remark': '',
                        'createtime': now,
                        'updatetime': now
                    }
                    bom_info_ser = FaKuyouBomInfoSerializer(data=bom_info)
                    bom_info_ser.is_valid(raise_exception=True)
                    bom_info_ser.save()
            # 工序2：复合料2
            if arr[17] is not None:
                status, goods = getGoodsInfoByCodeOrName({"goods_code": arr[16]})
                if status:
                    bom_info = {
                        'bom_id': bom_ser.data['id'],
                        'procedure_id': process_list[1]['id'],
                        'goods_id': goods['id'],
                        'goods_code': goods['goods_code'],
                        'goods_name': goods['goods_name'],
                        'unit_id': goods['unit_id'],
                        'default_unit': goods['unit_id'],
                        'purchase': 0,
                        'number': 1,
                        'basic_nmber': 1,
                        'money': 0,
                        'remark': '',
                        'createtime': now,
                        'updatetime': now
                    }
                    bom_info_ser = FaKuyouBomInfoSerializer(data=bom_info)
                    bom_info_ser.is_valid(raise_exception=True)
                    bom_info_ser.save()
            # 工序3：分切
            if arr[14] is not None and arr[15] is not None and arr[13] is not None:
                status, goods = getGoodsInfoByCodeOrName({"goods_code": arr[13]})
                if status:
                    unit_con = FaKuyouUnitConversion.objects.get(main_unit=goods['unit_id'])
                    bom_info = {
                        'bom_id': bom_ser.data['id'],
                        'procedure_id': process_list[2]['id'],
                        'goods_id': goods['id'],
                        'goods_code': goods['goods_code'],
                        'goods_name': goods['goods_name'],
                        'unit_id': goods['unit_id'],
                        'default_unit': goods['unit_id'],
                        'purchase': 0,
                        'number': round(arr[14] / unit_con.advance_rate / arr[15], 4),
                        'basic_nmber': round(arr[14] / unit_con.advance_rate / arr[15], 4),
                        'money': 0,
                        'remark': '',
                        'createtime': now,
                        'updatetime': now
                    }
                    bom_info_ser = FaKuyouBomInfoSerializer(data=bom_info)
                    bom_info_ser.is_valid(raise_exception=True)
                    bom_info_ser.save()
            # 工序4：折边
            if arr[18] is not None:
                pass
            # 工序5：制袋
            if arr[18] is not None or arr[19] is not None:
                # 制袋工序
                if arr[18] is not None:
                    status, goods = getGoodsInfoByCodeOrName({"goods_code": arr[18]})
                    if status:
                        unit_con = FaKuyouUnitConversion.objects.get(main_unit=goods['unit_id'])
                        bom_info = {
                            'bom_id': bom_ser.data['id'],
                            'procedure_id': process_list[4]['id'],
                            'goods_id': goods['id'],
                            'goods_code': goods['goods_code'],
                            'goods_name': goods['goods_name'],
                            'unit_id': goods['unit_id'],
                            'default_unit': goods['unit_id'],
                            'purchase': 0,
                            'number': round(arr[14] / unit_con.advance_rate / arr[15], 4),
                            'basic_nmber': round(arr[14] / unit_con.advance_rate / arr[15], 4),
                            'money': 0,
                            'remark': '',
                            'createtime': now,
                            'updatetime': now
                        }
                        bom_info_ser = FaKuyouBomInfoSerializer(data=bom_info)
                        bom_info_ser.is_valid(raise_exception=True)
                        bom_info_ser.save()
                if arr[19] is not None:
                    status, goods = getGoodsInfoByCodeOrName({"goods_code": arr[19]})
                    if status:
                        unit_con = FaKuyouUnitConversion.objects.get(main_unit=goods['unit_id'])
                        bom_info = {
                            'bom_id': bom_ser.data['id'],
                            'procedure_id': process_list[4]['id'],
                            'goods_id': goods['id'],
                            'goods_code': goods['goods_code'],
                            'goods_name': goods['goods_name'],
                            'unit_id': goods['unit_id'],
                            'default_unit': goods['unit_id'],
                            'purchase': 0,
                            'number': round(arr[14] / unit_con.advance_rate / arr[15], 4),
                            'basic_nmber': round(arr[14] / unit_con.advance_rate / arr[15], 4),
                            'money': 0,
                            'remark': '',
                            'createtime': now,
                            'updatetime': now
                        }
                        bom_info_ser = FaKuyouBomInfoSerializer(data=bom_info)
                        bom_info_ser.is_valid(raise_exception=True)
                        bom_info_ser.save()

addBom(r'C:\Users\Administrator\Desktop\bom清单.xlsx')

# if __name__ == "__main__":
#     IN_PATH = r'C:\Users\Administrator\Desktop\信息'
#     OUT_PATH = r'C:\Users\Administrator\Desktop\信息\原材料'
#     FILE_NAME = r'\原材料.xlsx'
#     try:
#         for i in ['单位成本', '期初数量']:
#             replace_zeros_with_openpyxl(
#                 input_file=IN_PATH + FILE_NAME,
#                 column_name=i,
#                 output_file=IN_PATH + FILE_NAME
#             )
#         sleep(1)
#         split_excel_with_format(
#             input_file=IN_PATH + FILE_NAME,
#             output_folder=OUT_PATH,
#             chunk_size=300,
#             header_rows=2
#         )
#
#         sleep(5)
#         batch_rename_extensions(OUT_PATH, "xlsm", "xlsx")
#     except Exception as e:
#         print(f"发生错误: {e}")
